﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using ICSharpCode.SharpZipLib.Zip;
using LumenWorks.Framework.IO.Csv;

namespace WorldIndex.MDGImport
{
    /// <summary>
    /// Handles import of MDG data to local database.
    /// </summary>
    public class MdgImport
    {
        private bool DownloadAndSQLInsertFinished = false;

        /// <summary>
        /// Downloads the MDG dataset from the web and installs into MSSQL database.
        /// </summary>
        public void DownloadAndInstallMdgDataset()
        {
            var locationPath = string.Empty;

            using (var webClient = new WebClient())
            {
                Console.WriteLine("Starting MDG dataset download...");
                webClient.DownloadFileCompleted += new System.ComponentModel.AsyncCompletedEventHandler(webClient_DownloadFileCompleted);
                webClient.DownloadProgressChanged += new DownloadProgressChangedEventHandler(webClient_DownloadProgressChanged);
                webClient.DownloadFileAsync(new Uri(WorldIndex.MDGImport.Properties.Resources.MdgDatasetUrl.ToString()), Constants.MdgFilenameZip);
            }

            while (!DownloadAndSQLInsertFinished)
            {
                System.Threading.Thread.Sleep(1000);
            }
        }

        /// <summary>
        /// Unzipping files.
        /// </summary>
        /// <param name="zippedFile">The file to unzip.</param>
        /// <returns>Unzipped file info.</returns>
        public FileInfo UnzipFile(FileInfo zippedFile)
        {
            var zipDirectory = new DirectoryInfo(zippedFile.DirectoryName + Constants.MdgUnzipFolder);

            if (zipDirectory.Exists)
            {
                zipDirectory.Delete(true);
            }

            var fastZip = new FastZip();
            fastZip.ExtractZip(zippedFile.FullName, zippedFile.DirectoryName + Constants.MdgUnzipFolder, string.Empty);
            IEnumerable<FileInfo> unzippedFiles = zipDirectory.GetFiles();

            if (unzippedFiles.Count() > 0)
            {
                unzippedFiles = unzippedFiles.OrderByDescending(f => f.CreationTime);
                return unzippedFiles.First();
            }

            return null;
        }

        public void InstallMdgDataIntoDatabase(FileInfo mdgDatasetCsv)
        {
            Console.WriteLine("Starting to install the MDG dataset into database from file: " + mdgDatasetCsv.FullName);

            var sqlScript = CreateMdgSeriesSqlScript(mdgDatasetCsv);

            using (var sqlConnection = new SqlConnection())
            {
                sqlConnection.ConnectionString = string.Format("Server={0};Database={1};Trusted_Connection=True;", Properties.Resources.MdgDatabaseServer, Properties.Resources.MdgDatabaseName);
                sqlConnection.Open();

                Console.WriteLine("Starting to write a total of " + sqlScript.Count + " rows to DB.");
                for (int i = 0; i < sqlScript.Count; i++)
                {
                    SqlCommand thisCommand = sqlConnection.CreateCommand();
                    thisCommand.CommandText = sqlScript[i];
                    thisCommand.ExecuteNonQuery();
                    Console.WriteLine("Row " + (i + 1) + " of " + sqlScript.Count + " - OK!");
                }
            }

            DownloadAndSQLInsertFinished = true;
        }

        public void InstallMdgDataIntoFile(FileInfo mdgDatasetCsv)
        {
            Console.WriteLine("Starting to install the MDG dataset into database from file: " + mdgDatasetCsv.FullName);

            var sqlScript = new List<string>();

            sqlScript = CreateMdgSeriesSqlScript(mdgDatasetCsv);

            using (TextWriter textWriter = File.CreateText(Properties.Resources.MdgTestFileSqlOutPath))
            {
                foreach (var script in sqlScript)
                {
                    textWriter.WriteLine(script);
                }
            }

            DownloadAndSQLInsertFinished = true;
        }

        public List<string> CreateMdgSeriesSqlScript(FileInfo MdgCsvFile)
        {
            var sqlScript = new List<string>();

            sqlScript.Add("DELETE FROM MdgSeriesData; DELETE FROM MdgSeriesDataFootnotes; DELETE FROM MdgCountry; DELETE FROM MdgSeriesDefinition; DELETE FROM MdgFootnotes; DELETE FROM MdgTypes;");

            using (var streamReader = new StreamReader(MdgCsvFile.FullName))
            {
                ////Reading footnotes
                var readingFootnotes = false;

                while (!streamReader.EndOfStream)
                {
                    var line = streamReader.ReadLine();
                    int indexOfDelimiter = line.IndexOf(',');

                    if (readingFootnotes)
                    {
                        if (string.IsNullOrEmpty(line))
                        {
                            break;
                        }

                        var scriptLine = string.Format("INSERT INTO MdgFootnotes VALUES ({0}, '{1}", line.Substring(0 + 1, indexOfDelimiter - 2), line.Substring(indexOfDelimiter + 2).Replace("'", "''"));

                        while (!line.EndsWith("\""))
                        {
                            line = streamReader.ReadLine();
                            scriptLine += string.Format(" {0}", line.Replace("'", "''"));
                        }

                        scriptLine = scriptLine.Substring(0, scriptLine.Length - 1);
                        scriptLine += string.Format("');");

                        sqlScript.Add(scriptLine);
                    }

                    if (indexOfDelimiter != -1 && line.Substring(0, indexOfDelimiter).Equals(Constants.MdgHeaderFootnoteCode))
                    {
                        readingFootnotes = true;
                    }
                }

                var readingTypes = false;

                while (!streamReader.EndOfStream)
                {
                    var line = streamReader.ReadLine();

                    var indexOfLimiters = new List<int>();

                    int counter = 0;
                    foreach (var character in line)
                    {
                        if (character.Equals(','))
                        {
                            indexOfLimiters.Add(counter);
                        }

                        counter++;
                    }

                    if (readingTypes)
                    {
                        if (string.IsNullOrEmpty(line))
                        {
                            break;
                        }

                        var scriptLine = string.Format("INSERT INTO MdgTypes VALUES ('{0}', '{1}', '{2}", line.Substring(0, indexOfLimiters[0]), line.Substring(indexOfLimiters[0] + 1, indexOfLimiters[1] - (indexOfLimiters[0] + 1)).Replace("'", "''"), line.Substring(indexOfLimiters[1] + 2).Replace("'", "''"));

                        while (!line.EndsWith("\""))
                        {
                            line = streamReader.ReadLine();
                            scriptLine += string.Format(" {0}", line.Replace("'", "''"));
                        }

                        scriptLine = scriptLine.Substring(0, scriptLine.Length - 1);
                        scriptLine += string.Format("');");

                        sqlScript.Add(scriptLine);
                    }

                    if (indexOfLimiters[0] != -1 && line.Substring(0, indexOfLimiters[0]).Equals(Constants.MdgHeaderTypeCode))
                    {
                        readingTypes = true;
                    }
                }
            }

            using (var csv = new CsvReader(new StreamReader(MdgCsvFile.FullName), false, ',', CsvReader.DefaultQuote, CsvReader.DefaultEscape, CsvReader.DefaultComment, true))
            {
                int fieldCount = csv.FieldCount;
                int valueGroups = 3;
                int mdgSeriesAddedCount = 1;
                var yearList = new List<string>();

                csv.ReadNextRecord();

                //Get years from table
                for (int i = 5; i < fieldCount; i = i + valueGroups)
                {
                    yearList.Add(csv[i]);
                }

                if (csv[0].ToString().Equals(Constants.MdgHeaderCountryCode))
                {
                    int previousCountryCode = -1;
                    var addedSeriesCodes = new List<int>();

                    while (csv.ReadNextRecord())
                    {
                        int newCountryCode = -1;
                        if (!Int32.TryParse(csv[0], out newCountryCode))
                        {
                            break;
                        }

                        //Inserting MDGCountry
                        if (previousCountryCode != newCountryCode)
                        {
                            previousCountryCode = newCountryCode;
                            sqlScript.Add(string.Format("INSERT INTO MdgCountry VALUES ({0}, '{1}');", csv[0], csv[1].Replace("'", "''")));
                        }

                        //Inserting MDGSeriesDefinition
                        int currentSeriesCode = Convert.ToInt32(csv[2]);
                        if (!addedSeriesCodes.Exists(t => t.Equals(currentSeriesCode)))
                        {
                            var isMdg = csv[3].Equals("Y") ? true : false;
                            sqlScript.Add(string.Format("INSERT INTO MdgSeriesDefinition VALUES ({0}, '{1}', '{2}');", csv[2], isMdg, csv[4].Replace("'", "''")));
                            addedSeriesCodes.Add(currentSeriesCode);
                        }

                        int loops = 0;

                        ////Inserting MDGSeriesData

                        for (int j = 5; j < fieldCount; j = j + valueGroups)
                        {

                            var mdgSeriesDataColumns = new string[6];
                            mdgSeriesDataColumns[0] = mdgSeriesAddedCount.ToString(); ////Id
                            mdgSeriesDataColumns[1] = csv[0]; ////CountryCode
                            mdgSeriesDataColumns[2] = csv[2]; ////SeriesCode

                            mdgSeriesDataColumns[3] = yearList[loops]; ////Year
                            loops++;

                            mdgSeriesDataColumns[4] = !string.IsNullOrEmpty(csv[j].Trim()) ? string.Format("'{0}'", csv[j].Trim()) : "null"; ////Value
                            var footnotes = string.Format("{0}", csv[j + 1].Trim()); ////Footnote
                            mdgSeriesDataColumns[5] = !string.IsNullOrEmpty(csv[j + 2].Trim()) ? string.Format("'{0}'", csv[j + 2].Trim()) : "null"; ////Type

                            if (mdgSeriesDataColumns[4] != "null" || mdgSeriesDataColumns[5] != "null" || !string.IsNullOrEmpty(footnotes))
                            {
                                //Adding MDG series data
                                sqlScript.Add(string.Format("INSERT INTO MdgSeriesData VALUES ({0}, {1}, {2}, '{3}', {4}, {5});", mdgSeriesDataColumns));
                                
                                //Adding footnotes
                                var footnoteList = footnotes.Split(new char[]{';'}, StringSplitOptions.RemoveEmptyEntries);
                                foreach (var footnoteId in footnoteList)
                                {
                                    sqlScript.Add(string.Format("INSERT INTO MdgSeriesDataFootnotes VALUES ({0}, {1});", mdgSeriesAddedCount, footnoteId.Trim()));
                                }

                                mdgSeriesAddedCount++;
                            }
                        }
                    }
                }
            }

            return sqlScript;
        }

        void webClient_DownloadFileCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                Console.WriteLine("MDG dataset download complete! " + e.Error);
                var unzippedFile = UnzipFile(new FileInfo(Constants.MdgFilenameZip));
                InstallMdgDataIntoDatabase(unzippedFile);
            }
            else
            {
                Console.WriteLine("Error downloading MDG dataset: " + e.Error);
            }
        }

        void webClient_DownloadProgressChanged(object sender, DownloadProgressChangedEventArgs e)
        {
            Console.WriteLine(string.Format("MDG dataset downloaded: {0} / {1}", e.BytesReceived, e.TotalBytesToReceive));
        }
    }
}
